package com.info.dpunified.dpgen;

import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.util.Date;

import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Color;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.ss.util.CellUtil;
import org.apache.poi.xssf.usermodel.XSSFColor;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.beans.factory.xml.XmlBeanFactory;
import org.springframework.core.io.ClassPathResource;
import com.info.dpunified.dpgen.service.DefectUploadService;
import com.info.dpunified.dpgen.service.ExcelUtil;

public class DPGenerator {

	/**
	 * @param args
	 * @throws IOException 
	 * @throws InvalidFormatException 
	 */
	public static void main(String[] args) throws IOException, InvalidFormatException 
	{

		//XmlBeanFactory beanFactory = new XmlBeanFactory(new ClassPathResource("spring-context.xml")) ;
		//DefectUploadService defectUploadService =(DefectUploadService) beanFactory.getBean("defectUploadService");
		//defectUploadService.insertDefects(null);
		
	    //Workbook wb = new XSSFWorkbook();
		//FileOutputStream fileOut = new FileOutputStream("D:/0.0-DPUnified/iomania/workbook.xlsx");
		
/*		FileOutputStream inp;
		XSSFWorkbook wb = null;
		try 
		{
			inp = new FileOutputStream("D:/0.0-DPUnified/iomania/DPToolOutputTemplate.xlsx",true);
			  wb = new XSSFWorkbook();
		} catch (FileNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (IOException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}*/
		
		//java.io.File file = new File("D:/0.0-DPUnified/iomania/DPToolOutputTemplate.xlsx"); 
		FileInputStream fis = new FileInputStream("D:/0.0-DPUnified/iomania/DPToolOutputTemplate.xlsx"); 
		org.apache.poi.ss.usermodel.Workbook wb = WorkbookFactory.create(fis); 
		fis.close(); 

		
		Object sValue = null; 
		Date dValue = null;
		//double nValue = 0;
		//boolean bValue = false;
		System.out.println("Removing summary sheet");
		wb.removeSheetAt(0);
		
		System.out.println("Adding summary sheet");
		Sheet sheet1 = wb.createSheet("Summary");
		wb.setSheetOrder("Summary",0);

		
/*	    CellStyle style, false, false, false, true = wb.createCellStyle();
	    style, false, false, false, true.setBorderLeft(CellStyle.BORDER_MEDIUM);
	    style, false, false, false, true.setLeftBorderColor(IndexedColors.BLACK.getIndex());	    

	    CellStyle styleRB = wb.createCellStyle();
	    styleRB.setBorderRight(CellStyle.BORDER_MEDIUM);
	    styleRB.setRightBorderColor(IndexedColors.BLACK.getIndex());

	    CellStyle styleBB = wb.createCellStyle();
	    styleBB.setBorderBottom(CellStyle.BORDER_MEDIUM);
	    styleBB.setBottomBorderColor(IndexedColors.BLACK.getIndex());*/
	    
		// Create a new font and alter it.
	    Font font = wb.createFont();
        font.setBoldweight(Font.BOLDWEIGHT_BOLD);
	    
        CellStyle boldStyle = wb.createCellStyle();
	    boldStyle.setFont(font);
	    
	    CellStyle style = wb.createCellStyle();

/*	    CellStyle boldstyle, false, false, false, true = wb.createCellStyle();
	    boldstyle, false, false, false, true.setFont(font);
	    boldstyle, false, false, false, true.setBorderLeft(CellStyle.BORDER_MEDIUM);
	    boldstyle, false, false, false, true.setLeftBorderColor(IndexedColors.BLACK.getIndex());	    

	    CellStyle boldStyleRB = wb.createCellStyle();
	    boldStyleRB.setFont(font);
	    boldStyleRB.setBorderRight(CellStyle.BORDER_MEDIUM);
	    boldStyleRB.setRightBorderColor(IndexedColors.BLACK.getIndex());	 

	    CellStyle boldStyleRBB = wb.createCellStyle();
	    boldStyleRBB.setFont(font);
	    boldStyleRBB.setBorderRight(CellStyle.BORDER_MEDIUM);
	    boldStyleRBB.setRightBorderColor(IndexedColors.BLACK.getIndex());
	    boldStyleRBB.setBorderBottom(CellStyle.BORDER_MEDIUM);
	    boldStyleRBB.setBottomBorderColor(IndexedColors.BLACK.getIndex());

	    CellStyle boldstyle, false, false, false, trueB = wb.createCellStyle();
	    boldstyle, false, false, false, trueB.setFont(font);
	    boldstyle, false, false, false, trueB.setBorderLeft(CellStyle.BORDER_MEDIUM);
	    boldstyle, false, false, false, trueB.setLeftBorderColor(IndexedColors.BLACK.getIndex());
	    boldstyle, false, false, false, trueB.setBorderBottom(CellStyle.BORDER_MEDIUM);
	    boldstyle, false, false, false, trueB.setBottomBorderColor(IndexedColors.BLACK.getIndex());
	    
	    */
	    CellStyle boldStyleGrey = wb.createCellStyle();
	    boldStyleGrey.setFont(font);
	    boldStyleGrey.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
	    boldStyleGrey.setFillPattern(CellStyle.SOLID_FOREGROUND);
	    
	    
	 // Fonts are set into a style so create a new one to use.
	    CellStyle boldCenterStyle = wb.createCellStyle();
	    boldCenterStyle.setFont(font);
	    boldCenterStyle.setAlignment(CellStyle.ALIGN_CENTER);	    
	    
	    
		String firstRow = "Defect Summary for period from dd-mmm-yy to dd-mmm-yy";
		
		ExcelUtil xutil = new ExcelUtil();
		
		int row = 1;
		int col = 1;
		
		xutil.addString(sheet1, row, col, row, col+8, firstRow, boldCenterStyle,false,false,false,false);		
		row++;
		col = 2;
		String secondRowH1 = "Classification by Action Taken";
		xutil.addString(sheet1, ++row, col, row, col+2, secondRowH1, boldStyleGrey,true,true,false,true);
		xutil.addString(sheet1, ++row, col, "", style, false,false,false,true);
		xutil.addString(sheet1, row, ++col, "Fixed");
		xutil.addNumber(sheet1, row, ++col, new Double(24),style, false,true,false,false);
		col = 2;
		xutil.addString(sheet1, ++row, col, "Total", boldStyle,false,false,true,false);
		xutil.addString(sheet1, row, ++col, "", style, false,false,true,false);
		xutil.addNumber(sheet1, row, ++col, new Double(24), boldStyle,false,true,true,false);

		row++;
		col = 2;
		String thirdRowH1 = "Classification by Type";
		xutil.addString(sheet1, ++row, col, thirdRowH1, boldStyleGrey,true,false,false,true);
		xutil.addString(sheet1, row, ++col, "", boldStyleGrey, true,false,false,false);
		xutil.addString(sheet1, row, ++col, "", boldStyleGrey, true,false,false,false);
		xutil.addString(sheet1, row, ++col, "Cum %", boldStyleGrey, true,true,false,false);
		col=2;
		xutil.addString(sheet1, ++row, col, "", style, false,false,false,true);
		xutil.addString(sheet1, row, ++col, "Configuration");
		xutil.addNumber(sheet1, row, ++col, new Double(6));
		xutil.addNumber(sheet1, row, ++col, new Double(30),style, false,true,false,false);
		col = 2;
		xutil.addString(sheet1, ++row, col, "", style, false,false,false,true);
		xutil.addString(sheet1, row, ++col, "Incorrect Solution");
		xutil.addNumber(sheet1, row, ++col, new Double(6));
		xutil.addNumber(sheet1, row, ++col, new Double(60),style, false,true,false,false);
		col = 2;
		xutil.addString(sheet1, ++row, col, "", style, false,false,false,true);
		xutil.addString(sheet1, row, ++col, "Incorrect Reqmts");
		xutil.addNumber(sheet1, row, ++col, new Double(4));
		xutil.addNumber(sheet1, row, ++col, new Double(80),style, false,true,false,false);

		col = 2;
		xutil.addString(sheet1, ++row, col, "", style, false,false,false,true);
		xutil.addString(sheet1, row, ++col, "Comments");
		xutil.addNumber(sheet1, row, ++col, new Double(2));
		xutil.addNumber(sheet1, row, ++col, new Double(90),style, false,true,false,false);

		col = 2;
		xutil.addString(sheet1, ++row, col, "", style, false,false,false,true);
		xutil.addString(sheet1, row, ++col, "Incomplete Reqmts");
		xutil.addNumber(sheet1, row, ++col, new Double(2));
		xutil.addNumber(sheet1, row, ++col, new Double(100),style, false,true,false,false);

		col = 2;
/*		xutil.addString(sheet1, ++row, col, "", style, false, false, false, true);
		xutil.addString(sheet1, row, ++col, "Logic");
		xutil.addNumber(sheet1, row, ++col, new Double(2), styleRB);
		col = 2;
		xutil.addString(sheet1, ++row, col, "", style, false, false, false, true);
		xutil.addString(sheet1, row, ++col, "Standards");
		xutil.addNumber(sheet1, row, ++col, new Double(2), styleRB);
		col = 2;*/
		
		xutil.addString(sheet1, ++row, col, "Total", boldStyle,false,false,true,true);
		xutil.addString(sheet1, row, ++col, "", style,false,false,true,false);
		xutil.addNumber(sheet1, row, ++col, new Double(20), boldStyle,false,true,true,false);

		System.out.println("Cleaning all non-required sheets");
		wb.removeSheetAt(1);
		wb.removeSheetAt(2);
		wb.removeSheetAt(3);
		wb.removeSheetAt(4);
		wb.setSheetName(5, "This is it");
		wb.removeSheetAt(6);
		wb.removeSheetAt(7);
		wb.removeSheetAt(8);
		wb.removeSheetAt(9);
		wb.removeSheetAt(10);
		System.out.println("Cleaning all non-required sheets - done");
		row++;
		col = 2;
		String fourthRowH1 ="Classification by Stage Detected";
		xutil.addString(sheet1, ++row, col, row, col+2, fourthRowH1, boldStyleGrey,true,true,false,true);
		xutil.addString(sheet1, ++row, col, "", style, false, false, false, true);
		xutil.addString(sheet1, row, ++col, "Build");
		xutil.addNumber(sheet1, row, ++col, new Double(20), style, false, true, false, false);
		col = 2;
		xutil.addString(sheet1, ++row, col, "", style, false, false, false, true);
		xutil.addString(sheet1, row, ++col, "System Testing");
		xutil.addNumber(sheet1, row, ++col, new Double(4), style, false, true, false, false);
		col = 2;
		xutil.addString(sheet1, ++row, col, "Total", boldStyle, false, false, false, true);
		xutil.addString(sheet1, row, ++col, "", style, false, false, true, false);
		xutil.addNumber(sheet1, row, ++col, new Double(24), boldStyle, false,true,true,false);
		
		row = 3;
		col = 7;
		String secondRowH2 ="Classification by Stage Injected";
		xutil.addString(sheet1, row, col, row, col+2, secondRowH2, boldStyleGrey,true,true,false,true);
		xutil.addString(sheet1, ++row, col, "", style, false, false, false, true);
		xutil.addString(sheet1, row, ++col, "Build");
		xutil.addNumber(sheet1, row, ++col, new Double(20), style, false, true, false, false);
		col = 7;
		xutil.addString(sheet1, ++row, col, "", style, false, false, false, true);
		xutil.addString(sheet1, row, ++col, "System Testing");
		xutil.addNumber(sheet1, row, ++col, new Double(4), style, false, true, false, false);
		col = 7;
		xutil.addString(sheet1, ++row, col, "Total", boldStyle, false, false, false, true);
		xutil.addString(sheet1, row, ++col, "", style, false, false, true, false);
		xutil.addNumber(sheet1, row, ++col, new Double(24), boldStyle, false, true, true, false);
		
		row++;
		col = 7;
		String thirdRowH2 ="Classification by Cause";
		xutil.addString(sheet1, ++row, col, row, col+2, thirdRowH2, boldStyleGrey,true,true,false,true);
		xutil.addString(sheet1, ++row, col, "", style, false, false, false, true);
		xutil.addString(sheet1, row, ++col, "Impact Not Analyzed");
		xutil.addNumber(sheet1, row, ++col, new Double(20), style, false, true, false, false);
		col = 7;
		xutil.addString(sheet1, ++row, col, "", style, false, false, false, true);
		xutil.addString(sheet1, row, ++col, "Insufficient Informn");
		xutil.addNumber(sheet1, row, ++col, new Double(4), style, false, true, false, false);
		col = 7;
		xutil.addString(sheet1, ++row, col, "", style, false, false, false, true);
		xutil.addString(sheet1, row, ++col, "Lack of Coordination");
		xutil.addNumber(sheet1, row, ++col, new Double(4), style, false, true, false, false);
		col = 7;
		xutil.addString(sheet1, ++row, col, "", style, false, false, false, true);
		xutil.addString(sheet1, row, ++col, "Stds. Not Followed");
		xutil.addNumber(sheet1, row, ++col, new Double(4), style, false, true, false, false);		
		col = 7;
		xutil.addString(sheet1, ++row, col, "", style, false, false, false, true);
		xutil.addString(sheet1, row, ++col, "Oversight");
		xutil.addNumber(sheet1, row, ++col, new Double(4), style, false, true, false, false);		
		col = 7;
		xutil.addString(sheet1, ++row, col, "Total", boldStyle, false, false, false, true);
		xutil.addString(sheet1, row, ++col, "", style, false, false, true, false);
		xutil.addNumber(sheet1, row, ++col, new Double(24), boldStyle, false, true, true, false);
		
		row++;		
		col = 7;
		String fourthRowH2 ="Classification by Severity";
		xutil.addString(sheet1, ++row, col, row, col+2, fourthRowH2, boldStyleGrey,true,true,false,true);
		xutil.addString(sheet1, ++row, col, "", style, false, false, false, true);
		xutil.addString(sheet1, row, ++col, "Minor");
		xutil.addNumber(sheet1, row, ++col, new Double(24), style, false, true, false, false);
		col = 7;
		xutil.addString(sheet1, ++row, col, "Total", boldStyle, false, false, false, true);
		xutil.addString(sheet1, row, ++col, "", style, false, false, true, false);
		xutil.addNumber(sheet1, row, ++col, new Double(24), boldStyle, false, true, true, false);
		
/*	    Sheet sheet2 = wb.createSheet("Pareto By Type");
	    Sheet sheet3 = wb.createSheet("Pareto By Cause");
	    Sheet sheet4 = wb.createSheet("DP Brainstorm");
	    Sheet sheet5 = wb.createSheet("Casual Analysis");
	    Sheet sheet6 = wb.createSheet("By Severity");
	    Sheet sheet7 = wb.createSheet("By Type");
	    Sheet sheet8 = wb.createSheet("By Cause");
	    Sheet sheet9 = wb.createSheet("by_Stage_Detected");
	    Sheet sheet10 = wb.createSheet("by_Stage_Injected");
	    Sheet sheet11 = wb.createSheet("RADARdata");*/
	    
	    //wb.write(fileOut);
		//fileOut.close();
	    //wb.write(inp);
		//inp.close();	
		FileOutputStream fos = new FileOutputStream("D:/0.0-DPUnified/iomania/DPToolOutputTemplate-1.xlsx"); 
		wb.write(fos); 
		
	}
		
}
